Amazon Mobile AnalyticsでエクスポートしたS3のデータを、Amazon Athenaで集計する #serverless #adventcalendar

はじめに

このエントリは Serverless Advent Calendar 2017 15日目の記事です。

Amazon Mobile Analytics はアプリケーションの利用に関する情報を収集・分析するためのサービスです。Auto Export機能を有効にすると、分析用のデータをS3およびRedshiftにエクスポートできます。高頻度でクエリを実行する場合はRedshiftが便利ですが、1日に数回クエリをかけるぐらいの利用頻度だとオーバースペックかもしれません。

今回はサーバレスなクエリサービス Amazon Athena を使って、 S3にエクスポートしたデータにクエリをかけてみます。

Amazon Mobile Analyticsのデータ

Amazon Mobile AnalyticsでS3へのAuto Export機能を有効にすると、任意のバケットにJSON形式のファイルを定期的に出力します。

今回は確認用のWEBページを用意してカスタムイベントを送信しました。

イベント送信処理

mobileAnalyticsClient.recordEvent('content-loaded', {
    'custom-id': 1, 'custom-name': 'index_page',
}, {});
mobileAnalyticsClient.submitEvents();

mobileAnalyticsClient.recordEvent('content-loaded', {
    'custom-id': 2, 'custom-name': 'another_page',
}, {});
mobileAnalyticsClient.submitEvents();

既定の項目に加えて、 custom-id と custom-name を記録します。

Amazon Athenaでクエリを実行する

eventsテーブルを作成

Create Table から Manually を選択してテーブルを定義します。

テーブルの基本情報

• Database : データベース名
• Table Name : テーブル名
• Location of Input Data Set : エクスポート先のS3のURL

今回はデータベース名を awsma 、 テーブル名を events に設定します。

入力データのフォーマット

入力データのフォーマットは JSON を選択します。

構成列を定義

S3に出力されるJSONのフォーマットは、公式ドキュメントに記載されています。

• Exported Event JSON (JSONの形式)

各項目を参照できるように、テーブルの構成列を定義します。 application や client など、入れ子のJSONを含むため、下記いずれかの方法で内側の項目を参照できるようにします。

• MAP型やSTRUCT型で定義する
• STRING型として定義して、射影するときに json_extract_scalar 関数を使う

今回は後者の方法を試してみます。前者の方法は下記の記事を参照してください。

• Amazon Athena Nested-JSONのSESログファイルを検索する | Developers.IO

構成列が多いので、 Bulk add columns で一括定義します。

下記の列を定義します。

event_type string,
event_timestamp bigint,
arrival_timestamp bigint,
event_version string,
application string,
client string,
device string,
session string,
monetization string,
attributes string,
metrics string

パーティション列を定義

S3にエクスポートされるデータは、下記の構造で配置されます。

• Amazon Mobile Analytics Auto Export - Writing Events to Amazon S3

年月日・時間ごとのフォルダを参照するため、パーティション列を追加します。今回は created_at 列として追加します。 パーティションについては下記の記事を参照してください。

• Amazon Athenaのパーティションを理解する | Developer.IO

ここまでで events テーブルの作成完了です。

パーティションの追加

S3にエクスポートされるデータは、Amazon Athenaが期待するパーティションの形式 <パーティション列>=<値> と異なる構造で配置されるので、 ALTER TABLE ADD PARTITION で明示的に配置場所を指定してパーティションを追加します。

ALTER TABLE
awsma.events
ADD
PARTITION
(created_at='2017-12-13_01')
LOCATION
's3:///awsma/events//2017/12/13/01'
PARTITION
(created_at='2017-12-13_02')
LOCATION
's3:///awsma/events//2017/12/13/02'
;

クエリ実行

検索できるようになったか確認します。

生データの確認

SELECT
*
FROM
awsma.events
WHERE
created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02'
;

定義した通りの内容で検索できるようになりました。

各項目を検索

タイムスタンプや入れ子のJSONはそのままだと確認しづらいので、わかりやすい形に変換します。

SELECT
from_unixtime(event_timestamp % 1000) AT TIME ZONE 'Asia/Tokyo' event_timestamp, -- (1) タイムスタンプ変換
from_unixtime(arrival_timestamp % 1000) AT TIME ZONE 'Asia/Tokyo' arrival_timestamp,
json_extract_scalar(application, '$.sdk.name') sdk_name, -- (2) 入れ子JSONから任意の項目取り出し
json_extract_scalar(application, '$.sdk.version') sdk_version,
json_extract_scalar(device, '$.platform.name') platform_name,
json_extract_scalar(device, '$.platform.version') platform_version,
json_extract_scalar(attributes, '$["custom-id"]') custom_id, -- (3) 入れ子JSONから任意の項目取り出し
json_extract_scalar(attributes, '$["custom-name"]') custom_name
FROM
awsma.events
WHERE
created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02'
ORDER BY
event_timestamp
;

• (1) エポックからの経過ミリ秒をタイムスタンプに変換します。今回はミリ秒以下を切り捨てていますが、 parse_duration 関数と組み合わせるとミリ秒も含めることができます。
• (2) json_extract_scalar 関数を使って任意の項目を取り出します。
• 第1引数: 列名を指定します
• 第2引数: JSONPath 形式で指定します
• (3) (2)と同様ですが、 . 区切りで指定できない文字 - を含むので、キーを文字列で指定してアクセスします。

カスタムイベントを集計

カスタムイベントのイベント名ごとに数を集計します。

SELECT
q.custom_name,
COUNT(1) event_count
FROM
(
SELECT
json_extract_scalar(attributes, '$["custom-name"]') custom_name
FROM
awsma.events
WHERE
created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02'
AND
event_type = 'content-loaded'
) q
GROUP BY
q.custom_name
ORDER BY
event_count DESC
;

おわりに

Amazon Athenaを活用して、 Amazon Mobile AnalyticsがエクスポートしたS3のデータにクエリを実行することができました。 日時のバッチ集計などにも利用できそうなので、積極的に活用していきたいですね！